using System;
using System.Collections.Generic;
using System.Data.SQLite;

using DataLite;
using DataLite.Values;

namespace DataLite.Data
{
    public class ColumnInfoDAO
    {
        #region Table name and table columns names
        /// <summary>
        /// Rowid column name
        /// </summary>
        public const string Cid = "cid";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Name = "name";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Type = "type";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Notnull = "notnull";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string DfltValue = "dflt_value";
        /// <summary>
        /// Table column name
        /// </summary>
        public const string Pk = "pk";

        #endregion

        #region Parameters names
        /// <summary>
        /// Sql parameter name
        /// </summary>
        public const string NameParam = "@name";

        #endregion

        #region Sql comands
        
        public const string sqlPragma_TableInfo = "PRAGMA table_info( {0} );";

        #endregion

        #region Singleton Instance

        private static ColumnInfoDAO instance = new ColumnInfoDAO();

        private ColumnInfoDAO() { }

        public static ColumnInfoDAO getInstance() { return instance; }

        #endregion

        #region ITableInfoDAO Members        

        public List<ColumnInfoVO> PragmaTableInfo(string table, string connectionString)
        {
            DataHelper dataHelper = new DataHelper(connectionString);
            List<ColumnInfoVO> tableInfoVO = PragmaTableInfo(table, dataHelper);
            dataHelper.Dispose();
            return tableInfoVO;
        }

        public List<ColumnInfoVO> PragmaTableInfo(string table, DataHelper dataHelper)
        {            
            List<ColumnInfoVO> columnInfoVOList = new List<ColumnInfoVO>();
            string sql = string.Format(sqlPragma_TableInfo, table);

            using (SQLiteDataReader dataReader = dataHelper.ExecuteReader(sql, null))
            {
                while (dataReader.Read())
                {
                    ColumnInfoVO columnInfoVO = MakeVO(dataReader, dataHelper);
                    columnInfoVOList.Add(columnInfoVO);
                }
            }
            return columnInfoVOList;
        }

        public ColumnInfoVO MakeVO(SQLiteDataReader dataReader, DataHelper dataHelper)
        {
            ColumnInfoVO tableInfoVO = new ColumnInfoVO();
            tableInfoVO.Cid = dataHelper.GetInt32(dataReader, Cid, 0);
            tableInfoVO.Name = dataHelper.GetString(dataReader, Name, String.Empty);
            tableInfoVO.Type = dataHelper.GetString(dataReader, Type, String.Empty);
            tableInfoVO.NotNull = dataHelper.GetInt32(dataReader, Notnull, 0);
            tableInfoVO.DfltValue = dataHelper.GetString(dataReader, DfltValue, "NULL");
            tableInfoVO.Pk = dataHelper.GetInt32(dataReader, Pk, 0);
            return tableInfoVO;
        }

        #endregion

    }
}
